﻿using Model;
using System;
using System.Collections.Generic;
using System.Data;
using Utility;
using Utility.Helper;

namespace BLL
{
    public class Qs_BusinessBLL : BaseBLL
    {
        public Qs_BusinessBLL(Service service)
        {
            this.Service = service;
        }

        public Qs_BusinessBLL()
        {
        }

        private Qs_SequencesBLL seqBll = new Qs_SequencesBLL();

        public QS_Business InsertModel(QS_Business model, string logonName)
        {
            //SQL 语句
            string sql = string.Format(@"insert into qs_business(n_business_id, n_business_name, n_process_id, n_business_state, n_business_operator_id, n_business_operator_time, n_remark,n_company_id)
values(" + model.Business_Id + ", '" + model.Business_Name + @"', " + model.Process_id + @", " + model.Business_State + @", " + model.Business_operator_Id + @", to_date('" + model.Business_operator_date + @"','yyyy-mm-dd HH24:mi:ss'), '" + model.Business_remark + "'," + model.N_COMPANY_ID + @")");
            //执行sql并接收返回结果

            if (base.NoQuery(sql, logonName) > 0)
            {
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 更新状态
        /// </summary>
        /// <param name="model">QS_Business实体</param>
        /// <param name="glb">关联表名</param>
        /// <param name="operName">操作用户名</param>
        /// <returns></returns>
        [Obsolete("部分代码需要修改")]
        public bool UpdateState(QS_Business model, string glb, string operName)
        {
            string sql1 = string.Format(@"UPDATE QS_Business SET N_business_id =" + model.Business_Id + @" , C_Business_Name ='" + model.Business_Name + @"' , N_PROCESS_ID =" + model.Process_id + @" ,n_business_state =" + model.Business_State + @" ,n_business_operator_id ='" + model.Business_operator_Id + @"' , n_business_operator_time ='" + model.Business_operator_date + @"', C_remark ='" + model.Business_remark + "'  WHERE N_business_id=" + model.Business_Id + "");
            switch (glb)
            {
                case "QS_APPLYMAIN":
                    break;

                case "QS_URGENTAPPLY":
                    break;

                default:
                    break;
            }
            bool count = DbHelperSQL.ExecuteSqlTran(sql1, operName);
            return count;
        }

        /// <summary>
        /// 添加附件
        /// </summary>
        /// <param name="model">QS_Business实体</param>
        /// <param name="glb">关联表名</param>
        /// <param name="operName">操作用户名</param>
        /// <returns></returns>
        public bool AddBuFile(QS_Business model, string glb, string operName)
        {
            string sql1 = "UPDATE THKQS.QS_Business SET N_BUSINESS_STATE=2 WHERE N_business_id=" + model.Business_Id;
            switch (glb)
            {
                case "QS_APPLYMAIN":
                    break;

                case "QS_URGENTAPPLY":
                    break;

                default:
                    break;
            }
            bool count = DbHelperSQL.ExecuteSqlTran(sql1, operName);
            return count;
        }

        #region 公司汇总新增代码

        public bool Insert(QS_Business entity)
        {
            var sqlP = InsertSqlP(entity);
            var count = NoQuery(sqlP.Item1, sqlP.Item2);
            return count > 0 ? true : false;
        }

        #endregion 公司汇总新增代码

        public Tuple<string, List<Tuple<string, string, string>>> InsertSqlP(QS_Business entity)
        {
            entity.Business_Id = GetSequence("SEQ_BUSINESS_ID");
            var sql = @"INSERT INTO QS_BUSINESS(
	N_BUSINESS_ID,
	N_BUSINESS_NAME,
	N_PROCESS_ID,
	N_BUSINESS_STATE,
	N_BUSINESS_OPERATOR_ID,
	N_BUSINESS_OPERATOR_TIME,
	N_REMARK,
	N_COMPANY_ID)
VALUES(
	:N_BUSINESS_ID,
	:N_BUSINESS_NAME,
	:N_PROCESS_ID,
	:N_BUSINESS_STATE,
	:N_BUSINESS_OPERATOR_ID,
	:N_BUSINESS_OPERATOR_TIME,
	:N_REMARK,
	:N_COMPANY_ID)";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":N_BUSINESS_ID", entity.Business_Id, "int");
            parms.Add(":N_BUSINESS_NAME", entity.Business_Name, "string");
            parms.Add(":N_PROCESS_ID", entity.Process_id, "int");
            parms.Add(":N_BUSINESS_STATE", entity.Business_State, "int");
            parms.Add(":N_BUSINESS_OPERATOR_ID", entity.Business_operator_Id, "int");
            parms.Add(":N_BUSINESS_OPERATOR_TIME", entity.Business_operator_date, "datetime");
            parms.Add(":N_REMARK", entity.Business_remark, "string");
            parms.Add(":N_COMPANY_ID", entity.N_COMPANY_ID, "int");
            return new Tuple<string, List<Tuple<string, string, string>>>(sql, parms);
        }

        public DataTable getAllBussiness()
        {
            string sql = "SELECT N_business_id,C_BUSINESS_NAME  FROM THKQS.QS_Business t WHERE N_BUSINESS_STATE=1 ORDER BY t.N_business_id DESC";
            DataTable dt = base.Query(sql, "", "", "", "");
            return dt;
        }

        public DataTable getFindById(string id)
        {
            string sql = "SELECT b.N_business_id,b.C_Business_Name,b.N_PROCESS_ID,b.n_business_state,b.n_business_operator_id,b.n_business_operator_time,b.C_remark FROM THKQS.QS_Business b WHERE b.N_business_id=" + id;
            DataTable dt = base.Query(sql, "", "", "", "");
            return dt;
        }

        public QS_Business GetBusinessById(decimal id)
        {
            string sql = "SELECT N_BUSINESS_ID,N_BUSINESS_NAME,N_PROCESS_ID,N_BUSINESS_STATE,N_BUSINESS_OPERATOR_ID,N_BUSINESS_OPERATOR_TIME,N_REMARK FROM QS_BUSINESS WHERE N_BUSINESS_ID=" + id.ToString();

            QS_Business model = null;
            DataTable dt = base.Query(sql, "", "", "", "");
            if (dt != null && dt.Rows.Count > 0)
            {
                model = new QS_Business();
                foreach (DataRow dr in dt.Rows)
                {
                    model.Business_Id = Convert.ToInt32(dr["N_BUSINESS_ID"].ToString());
                    model.Business_Name = dr["N_BUSINESS_NAME"].ToString();
                    model.Process_id = Convert.ToInt32(dr["N_PROCESS_ID"].ToString());
                    model.Business_State = Convert.ToInt32(dr["N_BUSINESS_STATE"].ToString());
                    model.Business_operator_Id = Convert.ToInt32(dr["N_BUSINESS_OPERATOR_ID"].ToString());
                    model.Business_operator_date = Convert.ToDateTime(dr["N_BUSINESS_OPERATOR_TIME"].ToString());
                    model.Business_remark = dr["N_REMARK"].ToString();
                }
            }
            return model;
        }

        public DataTable getBussinessById(string id)
        {
            string sql = @"SELECT B.N_BUSINESS_NAME 业务名称,
       c.cmy_name 公司名称,
       p.n_process_id,
       P.C_PROCESS_NAME 所属流程,
       p.n_process_id,
       U.USER_NAME 操作人员,
       U.USER_ID 操作人员ID,
       DECODE(B.N_BUSINESS_STATE, 0, '注销', 1, '办理中', '归档') AS 业务状态,
       to_char(B.N_BUSINESS_OPERATOR_TIME, 'yyyy-mm-dd HH24:MI:SS') 操作时间
  FROM QS_BUSINESS B, SYS_USER U, LC_PROCESS P, sys_company c
 WHERE B.N_PROCESS_ID = P.N_PROCESS_ID
   AND B.N_BUSINESS_OPERATOR_ID = U.USER_ID
   and b.n_company_id = c.cmy_id
   AND B.N_BUSINESS_ID =" + id;
            DataTable dt = base.Query(sql, "", "", "", "");
            return dt;
        }

        public DataTable getBussinessByPid(string pid, string operName)
        {
            string sql = "SELECT N_business_id,C_BUSINESS_NAME  FROM QS_Business t WHERE N_PROCESS_ID=" + pid;

            if (operName == "匿名用户")
            {
                sql += " AND N_BUSINESS_STATE=2";
            }
            else
            {
                sql += " AND N_BUSINESS_STATE IN (0,1,2)";
            }

            sql += " ORDER BY t.N_business_id DESC";

            DataTable dt = base.Query(sql, "", "", "", "");

            return dt;
        }

        public DataTable getBussinessByPid(string pid, string operName, decimal depid)
        {
            string sql = "SELECT N_business_id,C_BUSINESS_NAME  FROM QS_Business t WHERE N_PROCESS_ID=" + pid;

            if (operName == "匿名用户" || depid != 149)
            {
                sql += " AND N_BUSINESS_STATE=2";
            }
            else
            {
                sql += " AND N_BUSINESS_STATE IN (0,1,2)";
            }

            sql += " ORDER BY t.N_business_id DESC";

            DataTable dt = base.Query(sql, "", "", "", "");

            return dt;
        }

        public DataTable getBussinessGc()
        {
            string sql = @"SELECT N_business_id,C_BUSINESS_NAME  FROM THKQS.QS_Business t WHERE  N_PROCESS_ID=108 AND N_BUSINESS_STATE=2
    AND t.N_business_id NOT IN (
	SELECT
		x.N_business_id
	FROM
		JX_GCXZAPPLY x
	WHERE
		x.N_GCXZID IN (
			SELECT
				k.N_GCXZID
			FROM
				JX_GCKHAPPLY k
			WHERE
				k.N_state_id IN (1, 2)
		)
    ) ORDER BY t.N_business_id DESC";//流程号为108号的，即为修订过程考核细则流程下的，已归档的业务
            DataTable dt = base.Query(sql, "", "", "", "");

            return dt;
        }

        public DataTable getBussinessGd()
        {
            string sql = "SELECT N_business_id,C_BUSINESS_NAME,N_PROCESS_ID FROM THKQS.QS_Business t WHERE N_BUSINESS_STATE=2 ORDER BY t.N_business_id DESC";
            DataTable dt = base.Query(sql, "", "", "", "");

            return dt;
        }

        public DataTable GetMessage(string DepId)
        {
            string sql = @"SELECT * FROM Message WHERE ','+rtrim(C_depids) like '%," + DepId + ",%' ORDER BY MESSAGE_ID DESC,D_OPDATE DESC";
            DataTable dt = base.Query(sql, "", "", "", "");
            return dt;
        }

        public DataTable ShowMessage(string MsId)
        {
            string sql = @"SELECT *
		FROM
			Message
		WHERE
			MESSAGE_ID=" + MsId;
            DataTable dt = base.Query(sql, "", "", "", "");
            return dt;
        }

        public DataTable ShowMessage(string MsName, string DepId)
        {
            string sql = @"SELECT *
		FROM
			Message
		WHERE
			C_TITLE='" + MsName + "' AND C_DEPIDS='" + DepId + "' ORDER BY MESSAGE_ID DESC";
            DataTable dt = base.Query(sql, "", "", "", "");
            return dt;
        }

        public int GetRecordCount(string whereStr)
        {
            string sql = @"SELECT count(1)
FROM
    THKQS.QS_Business b,
    THKQS.Lc_Process p,
    THKQS.Sys_User u
WHERE
    b.N_PROCESS_ID = p.N_PROCESS_ID
    AND b.N_BUSINESS_OPERATOR_ID = u.USER_ID
    AND p.N_PROCESS_STATE=1 ";
            if (!string.IsNullOrEmpty(whereStr))
            {
                sql += whereStr;
            }
            var dt = base.Query(sql, "", "", "", "");
            return Convert.ToInt32(dt.Rows[0][0]);
        }

        public bool UpdateYwState(string bid, int State, string operName)
        {
            string sql = "UPDATE THKQS.QS_Business SET n_business_state =" + State + " WHERE N_business_id=" + bid;
            return base.NoQuery(sql, operName) > 0 ? true : false;
        }

        public bool IsGD(string bid)
        {
            string sql = string.Format(@"SELECT COUNT(*) FROM QS_Business WHERE N_business_id=" + bid + " AND n_business_state=2");
            return base.GetCount(sql) > 0 ? true : false;
        }

        public bool IsZX(string bid)
        {
            string sql = string.Format(@"SELECT COUNT(*) FROM THKQS.QS_Business WHERE N_business_id=" + bid + " AND n_business_state=0");
            return base.GetCount(sql) > 0 ? true : false;
        }

        public bool IsJXHT(string bid)
        {
            try
            {
                string sql = @"SELECT C_remark FROM QS_Business WHERE N_business_id=" + bid;

                DataTable dt = base.Query(sql, "", "", "", "");
                if (dt.Rows.Count > 0)
                {
                    if (dt.Rows[0][0].ToString().Split('@')[1].ToString() == "绩效合同")
                    {
                        return true;
                    }
                }
                return false;
            }
            catch
            {
                return false;
            }
        }

        public string getJD(string bid)
        {
            string sql = @"SELECT C_remark FROM QS_Business WHERE N_business_id=" + bid;

            DataTable dt = base.Query(sql, "", "", "", "");
            if (dt.Rows.Count > 0)
            {
                try
                {
                    return dt.Rows[0][0].ToString().Split('@')[1].ToString();
                }
                catch
                {
                    return "";
                }
            }
            else
            {
                return "";
            }
        }

        public bool IsJSKH(string bid)
        {
            try
            {
                string sql = @"SELECT C_remark FROM QS_Business WHERE N_business_id=" + bid;
                DataTable dt = base.Query(sql, "", "", "", "");
                if (dt.Rows.Count > 0)
                {
                    if (dt.Rows[0][0].ToString().Split('@')[1].ToString() == "结算考核")
                    {
                        return true;
                    }
                }
                return false;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 根据业务编号查询
        /// </summary>
        /// <param name="business"></param>
        /// <returns></returns>
        public DataTable GetBusiness(decimal business)
        {
            string sql = string.Format(@"select b.n_business_id,b.n_business_name ,decode(m.state,0,'正在办理',1,'归档',2,'注销') state ,c.cmy_name,u.user_name,to_char(m.applydate, 'yyyy-mm-dd HH24:MI:SS') applydate
 from qs_business b,qs_applymain m,sys_company c,sys_user u
where b.n_business_id=m.n_business and m.companyid=c.cmy_id and u.user_name=m.applyperson and m.n_business=" + business + "");

            DataTable dt = base.Query(sql, "", "", "", "");
            return dt;
        }

        public decimal GetBusinId()
        {
            string sql = "select seq_business_id.nextval from dual";
            DataTable dt = base.Query(sql, "", "", "", "");
            return decimal.Parse(dt.Rows[0][0].ToString());
        }

        /// <summary>
        /// 获取序列号
        /// </summary>
        /// <returns></returns>
        public decimal GetYearDid(string name)
        {
            string sql = "select " + name + @".nextval from dual";
            DataTable dt = base.Query(sql, "", "", "", "");
            return decimal.Parse(dt.Rows[0][0].ToString());
        }

        /// <summary>
        /// 年计划申请
        /// </summary>
        /// <param name="bModle"></param>
        /// <param name="pModel"></param>
        /// <param name="list"></param>
        /// <param name="tModel"></param>
        /// <param name="NexModel"></param>
        /// <returns></returns>
        public int AppPlanYear(QS_Business bModle, QX_PLANYEARAPPLY pModel, List<QX_PLANYEAR_DETAIL> list, Lc_Transaction tModel, Lc_Transaction NexModel, string logname)
        {
            List<string> cList = new List<string>();
            string sql = @"insert into qs_business
  (n_business_id,
   n_business_name,
   n_process_id,
   n_business_state,
   n_business_operator_id,
   n_business_operator_time,
   n_remark,
   n_company_id)
values
  (" + bModle.Business_Id + @",
   '" + bModle.Business_Name + @"',
   " + bModle.Process_id + @",
  " + bModle.Business_State + @",
   " + bModle.Business_operator_Id + @",
   sysdate,
 '" + bModle.Business_remark + @"',
      " + bModle.N_COMPANY_ID + @")";

            cList.Add(sql);

            sql = @"insert into qx_planyearapply
  (n_yearapp_id,
   n_year,
   n_business_id,
   c_yearapp_name,
   n_company_id,
   n_user_id,
   d_operator_time,
   c_reamrk,N_BUSINESS_STATE)
values
  (" + pModel.N_YEARAPP_ID + @",
  " + pModel.N_YEAR + @",
   " + pModel.N_BUSINESS_ID + @",
   '" + pModel.C_YEARAPP_NAME + @"',
   " + pModel.N_COMPANY_ID + @",
   " + pModel.N_USER_ID + @",
   sysdate,
   '" + pModel.C_REAMRK + @"',1)";

            cList.Add(sql);

            for (int i = 0; i < list.Count; i++)
            {
                #region 明细

                QX_PLANYEAR_DETAIL dModel = list[i];

                sql = @"
insert into qx_planyear_detail
  (n_ydetail_id,
   n_yearapp_id,
   n_proid,
   c_proname,
   n_amount,
   c_remark,
   n_operatorid,
   d_operatortime,N_BUSINESS_ID,N_TYPE,
C_PRONUM,N_TYPE2,N_NUM1,N_NUM2,N_PARENTID)
values
  ( SEQ_N_YDETAIL_ID.Nextval ,
   " + dModel.N_YEARAPP_ID + @",
   " + dModel.N_PROID + @",
   '" + dModel.C_PRONAME + @"',
 " + dModel.N_AMOUNT + @",
  '" + dModel.C_REMARK + @"',
 " + dModel.N_OPERATORID + @",
   sysdate," + dModel.N_BUSINESS_ID + @"," + dModel.N_TYPE + @",'" + dModel.C_PRONUM + @"',
   " + dModel.N_TYPE2 + @"," + dModel.N_NUM1 + @"," + dModel.N_NUM2 + @"," + dModel.N_PARENTID + @")";

                cList.Add(sql);

                #endregion 明细
            }

            sql = @"insert into lc_transaction
  (n_transaction_id,
   n_process_id,
   n_queue_id,
   n_business_id,
   n_transaction_state,
   n_transaction_comment,
   c_transactor_id,
   d_transaction_time_begin,
   d_transaction_time_end,
   d_transaction_time_due,
   n_transaction_due_state,
   c_transactor_signature,
   n_last_bace_transaction_id)
values
  (n_transaction_id.Nextval,
   " + tModel.N_PROCESS_ID + @",
   " + tModel.N_QUEUE_ID + @",
   " + bModle.Business_Id + @",
   " + tModel.N_TRANSACTION_STATE + @",
   '" + tModel.C_TRANSACTION_COMMENT + @"',
   '" + tModel.C_TRANSACTOR_ID + @"',
   sysdate,
   sysdate,
   TO_DATE('" + tModel.D_TRANSACTION_TIME_DUE + @"','YYYY-MM-DD HH24:MI:SS'),
    " + tModel.N_TRANSACTION_DUE_STATE + @",
   '" + tModel.C_TRANSACTOR_SIGNATURE + @"',
   " + tModel.N_LAST_BACE_TRANSACTION_ID + @")";
            cList.Add(sql);

            sql = @"insert into lc_transaction
  (n_transaction_id,
   n_process_id,
   n_queue_id,
   n_business_id,
   n_transaction_state,
   n_transaction_comment,
   c_transactor_id,
   d_transaction_time_begin,
   d_transaction_time_end,
   d_transaction_time_due,
   n_transaction_due_state,
   c_transactor_signature,
   n_last_bace_transaction_id)
values
  (n_transaction_id.Nextval,
   " + NexModel.N_PROCESS_ID + @",
   " + NexModel.N_QUEUE_ID + @",
   " + bModle.Business_Id + @",
   " + NexModel.N_TRANSACTION_STATE + @",
   '" + NexModel.C_TRANSACTION_COMMENT + @"',
   '" + NexModel.C_TRANSACTOR_ID + @"',
   sysdate,
   TO_DATE('1900-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'),
   TO_DATE('" + NexModel.D_TRANSACTION_TIME_DUE + @"','YYYY-MM-DD HH24:MI:SS'),
    " + NexModel.N_TRANSACTION_DUE_STATE + @",
   '" + NexModel.C_TRANSACTOR_SIGNATURE + @"',
   " + NexModel.N_LAST_BACE_TRANSACTION_ID + @")";

            cList.Add(sql);

            int rec = base.ThkService.SQLSExcute(cList, "年计划申请", "AppPlanYear", logname, logname);
            return rec;
        }
    }
}